Release 10.1A: OpenEdge Data Management:
SQL Reference

Date arithmetic expressions

Date arithmetic expressions compute the difference between date-time expressions in terms of days or milliseconds. SQL supports these forms of date arithmetic:

Addition and subtraction of integers to and from date-time expressions.
Subtraction of one date-time expression from another.

This is the syntax for a date arithmetic expression:

Syntax

date_time_expr { + | - } int_expr | date_time_expr - date_time_expr

date_time_expr

Returns a value of type DATE or TIME or TIMESTAMP. A single date-time expression cannot mix data types, however. All elements of the expression must be the same data type.

Date-time expressions can contain date-time literals, but they must be converted to DATE or TIME using the CAST, CONVERT, or TO_DATE functions.

int_expr

Returns an integer value. SQL interprets the integer differently depending on the data type of the date-time expression:

For DATE expressions, integers represent days.
For TIME expressions, integers represent milliseconds.
For TIMESTAMP expressions, integers represent milliseconds.

Examples

The following example manipulates DATE values using date arithmetic. SQL interprets integers as days and returns date differences in units of days:

SELECT C1, C2, C1-C2 FROM DTEST c1 c2 c1-c2 --------------------------------------- 1956-05-07 1952-09-29 1316 select sysdate, sysdate - 3 , sysdate - cast ('9/29/52' as date) from dtest; sysdate sysdate-3 sysdate-convert(date,9/29/52) ---------------------------------------------------------- 1995-03-24 1995-03-21 15516

The following example manipulates TIME values using date arithmetic. SQL interprets integers as milliseconds and returns time differences in milliseconds:

select systime, systime - 3000, systime - cast ('15:28:01' as time) from dtest; systime systime-3000 systime-convert(time,15:28:01) ---------------------------------------------------------- 15:28:09 15:28:06 8000